CREATE DATABASE bookstore;
USE bookstore;
#users 用户表
CREATE TABLE users
(
	id VARCHAR(40) PRIMARY KEY, #主键
	username VARCHAR(20) UNIQUE, #用户名
	PASSWORD VARCHAR(30), #密码
	email VARCHAR(40) #邮箱
);

#book表
DROP TABLE IF EXISTS `t_book`;
CREATE TABLE `t_book` (
  `id` int(40) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
  `price` decimal(11,2) DEFAULT NULL,
  `author` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
  `sales` int(11) DEFAULT NULL,
  `stock` int(11) DEFAULT NULL,
  `img_path` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ----------------------------
--  Records of `t_book`
-- ----------------------------
BEGIN;
INSERT INTO `t_book` VALUES ('1', 'java 从入门到放弃', '80.00', '国哥', '999', '9', 'static/img/default.jpg');
COMMIT;



#Cart 购物车
CREATE TABLE cart
(

	id  VARCHAR(30), #商品id
	totalPrice decimal(11,2), #购物车中总价格
	totalCount int(4) #购物车中商品总数量
);


drop TABLE if EXISTS `t_order`;
create TABLE t_order(

	`order_id` varchar(100) PRIMARY key not null,
	`create_time` datetime,
	`price` DECIMAL(11,2),
	`status` int,
	`user_id` VARCHAR(100),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)

);



drop TABLE if EXISTS `t_order_item`;
create TABLE t_order_item(


	`id` int PRIMARY key auto_increment,
	`name` varchar(100),
	`count` int,
	`price` decimal(11,2),
	`total_price` decimal(11,2),
	`order_id` varchar(50),
	FOREIGN key(`order_id`) REFERENCES t_order(`order_id`)

);
ALTER TABLE t_order_item MODIFY `name` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
